"""
Offspring Tracking API routes - Offspring Tracking Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from db.connection import execute_query

bp = Blueprint('offspring', __name__, url_prefix='/api/offspring')

@bp.route('', methods=['GET'])
def get_offspring():
    """
    Get all offspring records with optional filtering and pagination
    GET /api/offspring?donor_id=1&gender=男&page=1&pageSize=10
    """
    try:
        donor_id = request.args.get('donor_id', '').strip()
        recipient_id = request.args.get('recipient_id', '').strip()
        gender = request.args.get('gender', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT ot.offspring_id, ot.date_of_birth, ot.gender,
                   ot.reported_by, ot.report_date,
                   d.donor_id, d.donor_code, d.full_name as donor_name,
                   r.recipient_id, r.full_name as recipient_name
            FROM offspring_tracking ot
            JOIN donors d ON ot.donor_id = d.donor_id
            JOIN recipients r ON ot.recipient_id = r.recipient_id
            WHERE 1=1
        """
        
        params = []
        # 只在参数不为空字符串时添加过滤条件
        if donor_id:
            query += " AND ot.donor_id = %s"
            params.append(int(donor_id))
        if recipient_id:
            query += " AND ot.recipient_id = %s"
            params.append(int(recipient_id))
        if gender:
            query += " AND ot.gender = %s"
            params.append(gender)
        
        query += " ORDER BY ot.offspring_id ASC"
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        offspring = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(*) as count
            FROM offspring_tracking ot
            WHERE 1=1
        """
        count_params = []
        
        if donor_id:
            count_query += " AND ot.donor_id = %s"
            count_params.append(int(donor_id))
        if recipient_id:
            count_query += " AND ot.recipient_id = %s"
            count_params.append(int(recipient_id))
        if gender:
            count_query += " AND ot.gender = %s"
            count_params.append(gender)
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': offspring,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:offspring_id>', methods=['GET'])
def get_offspring_detail(offspring_id):
    """
    Get a single offspring record by ID
    GET /api/offspring/:id
    """
    try:
        query = """
            SELECT ot.*,
                   d.donor_code, d.full_name as donor_name,
                   r.full_name as recipient_name
            FROM offspring_tracking ot
            JOIN donors d ON ot.donor_id = d.donor_id
            JOIN recipients r ON ot.recipient_id = r.recipient_id
            WHERE ot.offspring_id = %s
        """
        
        offspring = execute_query(query, (offspring_id,), fetch_one=True)
        
        if not offspring:
            return jsonify({
                'code': 404,
                'message': 'Offspring record not found'
            }), 404
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': offspring
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('', methods=['POST'])
def create_offspring():
    """
    Create a new offspring record
    POST /api/offspring
    """
    try:
        data = request.get_json()
        
        query = """
            INSERT INTO offspring_tracking 
            (donor_id, recipient_id, date_of_birth, gender, reported_by, report_date)
            VALUES (%s, %s, %s, %s, %s, %s)
            RETURNING offspring_id
        """
        
        result = execute_query(
            query,
            (
                data['donor_id'],
                data['recipient_id'],
                data['date_of_birth'],
                data['gender'],
                data.get('reported_by'),
                data.get('report_date')
            ),
            fetch_one=True
        )
        
        return jsonify({
            'code': 201,
            'message': 'Offspring record created successfully',
            'data': {'offspring_id': result['offspring_id']}
        }), 201
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:offspring_id>', methods=['PUT'])
def update_offspring(offspring_id):
    """
    Update an offspring record
    PUT /api/offspring/:id
    """
    try:
        data = request.get_json()
        
        query = """
            UPDATE offspring_tracking
            SET donor_id = %s,
                recipient_id = %s,
                date_of_birth = %s,
                gender = %s,
                reported_by = %s,
                report_date = %s
            WHERE offspring_id = %s
        """
        
        execute_query(
            query,
            (
                data['donor_id'],
                data['recipient_id'],
                data['date_of_birth'],
                data['gender'],
                data.get('reported_by'),
                data.get('report_date'),
                offspring_id
            ),
            fetch=False
        )
        
        return jsonify({
            'code': 200,
            'message': 'Offspring record updated successfully'
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:offspring_id>', methods=['DELETE'])
def delete_offspring(offspring_id):
    """
    Delete an offspring record
    DELETE /api/offspring/:id
    """
    try:
        query = "DELETE FROM offspring_tracking WHERE offspring_id = %s"
        execute_query(query, (offspring_id,), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Offspring record deleted successfully'
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

